Amazon Redshift: 新しいデータ型『タイムゾーン付きタイムスタンプ(TIMESTAMPTZ)』が使用可能になりました

昨日(9/30)、私の"心の映画"とも言えるべき『マッドマックス 怒りのデスロード』が"極上爆音上映"を売りにしているシネマシティ(立川市)にてリバイバル上映を終了致しました。最終上映という事で映画開始前の挨拶を観客の中から選んだという事らしいのですが、その中から選ばれたのはV140(当映画を映画館で140回鑑賞)且つV50(シネマシティにて50回鑑賞)の女性の方だったようです。途方も無い数字過ぎてびっくりしたのと共に、通算V17(うちシネマシティではV11)までしか達成出来ていない私はまだまだ修行とV8への崇拝が足りないなぁと思う次第でした。

という訳で本題です。

Amazon Redshiftでは、タイムゾーンについては永らく『UTC(世界協定時刻)』のみ使用可となっており、利用可能なデータ型についても『timestamp without time zone』(タイムゾーン無しのタイムスタンプ型)のみとなっておりました。

でしたが、この度ようやく新しく『timestamp with time zone』(タイムゾーン付きタイムスタンプ型)が利用可能となった事がアナウンスされました！当エントリではその内容について紹介してみたいと思います。

Amazon Redshift introduces new data type to support time zones in time stamps! https://t.co/rvu7qEmZxU pic.twitter.com/6WMCp8om2h

— Amazon Web Services (@awscloud) 2016年9月30日

利用可能となる条件

毎度の事ながら、Redshiftに於いては新機能リリースは所定のパッチが当たり、条件を満たすバージョンにアップグレードされるか、所定のバージョンでクラスタが作成出来るタイミングでクラスタを新規作成する必要があります。下記フォーラムでのアナウンス内容によると、これから1〜2週間の間に(所定の時間に設定した)メンテナンスウインドウでのパッチで更新される予定、とあります。

• AWS Developer Forums: Amazon Redshift Maintenance (September 29th – October 13th, 2016)

バージョンについては1.0.1103になっていれば使えるようです。現行クラスタのバージョンが幾つになっているかを確かめるには管理コンソールで内容を確認するか、AWS CLIで以下のコマンドを実行する事で内容を見ることが出来ます。

$ aws redshift describe-clusters --cluster-identifier (クラスタ名) \
 | jq '.Clusters[] | .ClusterVersion, .ClusterRevisionNumber'
"1.0"
"1101"

新しいデータ型：TIMESTAMPTZ

• Datetime Types: TIMESTAMPTZ - Amazon Redshift

TIMESTAMPTZはTIMESTAMP WITH TIME ZONE(タイムゾーン付きタイムスタンプ)のエイリアスです。

日付、時刻、そしてタイムスタンプの情報を持つ"完全な"タイムスタンプ値を入力する際に、このデータ型:TIMESTAMPTZを使います。入力値にタイムゾーンが含まれていると、Amazon Redshiftはその値を世界協定時刻(UTC)に変換し、UTCとして値を格納します。

サポートされているタイムゾーンのリストを観る場合はpg_timezone_names()を使います。 (少し長いですが実行内容も添付しておきます)

# select pg_timezone_names();
                 pg_timezone_names                  
----------------------------------------------------
 (Africa/Banjul,GMT,00:00:00,f)
 (Africa/Dar_es_Salaam,EAT,03:00:00,f)
 (Africa/Blantyre,CAT,02:00:00,f)
 (Africa/Gaborone,CAT,02:00:00,f)
 (Africa/Maputo,CAT,02:00:00,f)
 (Africa/Monrovia,GMT,00:00:00,f)
 (Africa/Khartoum,EAT,03:00:00,f)
 (Africa/Djibouti,EAT,03:00:00,f)
 (Africa/Johannesburg,SAST,02:00:00,f)
 (Africa/Freetown,GMT,00:00:00,f)
 (Africa/Juba,EAT,03:00:00,f)
 (Africa/Cairo,EET,02:00:00,f)
 (Africa/Maseru,SAST,02:00:00,f)
 (Africa/Malabo,WAT,01:00:00,f)
 (Africa/Sao_Tome,GMT,00:00:00,f)
 (Africa/Lome,GMT,00:00:00,f)
 (Africa/Lagos,WAT,01:00:00,f)
 (Africa/Addis_Ababa,EAT,03:00:00,f)
 (Africa/Accra,GMT,00:00:00,f)
 (Africa/Nairobi,EAT,03:00:00,f)
 (Africa/Algiers,CET,01:00:00,f)
 (Africa/Asmera,EAT,03:00:00,f)
 (Africa/Porto-Novo,WAT,01:00:00,f)
 (Africa/Nouakchott,GMT,00:00:00,f)
 (Africa/Asmara,EAT,03:00:00,f)
 (Africa/Abidjan,GMT,00:00:00,f)
 (Africa/Douala,WAT,01:00:00,f)
 (Africa/Lubumbashi,CAT,02:00:00,f)
 (Africa/Bangui,WAT,01:00:00,f)
 (Africa/Ouagadougou,GMT,00:00:00,f)
 (Africa/Windhoek,WAST,02:00:00,t)
 (Africa/Conakry,GMT,00:00:00,f)
 (Africa/Ceuta,CEST,02:00:00,t)
 (Africa/Luanda,WAT,01:00:00,f)
 (Africa/Dakar,GMT,00:00:00,f)
 (Africa/Lusaka,CAT,02:00:00,f)
 (Africa/Mogadishu,EAT,03:00:00,f)
 (Africa/Tunis,CET,01:00:00,f)
 (Africa/Kinshasa,WAT,01:00:00,f)
 (Africa/Tripoli,CEST,02:00:00,t)
 (Africa/Niamey,WAT,01:00:00,f)
 (Africa/Bujumbura,CAT,02:00:00,f)
 (Africa/Bamako,GMT,00:00:00,f)
 (Africa/Kampala,EAT,03:00:00,f)
 (Africa/Casablanca,WET,00:00:00,f)
 (Africa/Kigali,CAT,02:00:00,f)
 (Africa/Ndjamena,WAT,01:00:00,f)
 (Africa/Bissau,GMT,00:00:00,f)
 (Africa/Timbuktu,GMT,00:00:00,f)
 (Africa/Mbabane,SAST,02:00:00,f)
 (Africa/El_Aaiun,WET,00:00:00,f)
 (Africa/Libreville,WAT,01:00:00,f)
 (Africa/Harare,CAT,02:00:00,f)
 (Africa/Brazzaville,WAT,01:00:00,f)
 (EST5EDT,EDT,-04:00:00,t)
 (CST6CDT,CDT,-05:00:00,t)
 (PST8PDT,PDT,-07:00:00,t)
 (America/Boa_Vista,AMT,-04:00:00,f)
 (America/Edmonton,MDT,-06:00:00,t)
 (America/Louisville,EDT,-04:00:00,t)
 (America/Mendoza,ART,-03:00:00,f)
 (America/Menominee,CDT,-05:00:00,t)
 (America/Cordoba,ART,-03:00:00,f)
 (America/Rio_Branco,AMT,-04:00:00,f)
 (America/Costa_Rica,CST,-06:00:00,f)
 (America/Moncton,ADT,-03:00:00,t)
 (America/Thule,ADT,-03:00:00,t)
 (America/Managua,CST,-06:00:00,f)
 (America/Barbados,AST,-04:00:00,f)
 (America/Santarem,BRT,-03:00:00,f)
 (America/Martinique,AST,-04:00:00,f)
 (America/Kentucky/Louisville,EDT,-04:00:00,t)
 (America/Kentucky/Monticello,EDT,-04:00:00,t)
 (America/Eirunepe,AMT,-04:00:00,f)
 (America/Belize,CST,-06:00:00,f)
 (America/Resolute,CDT,-05:00:00,t)
 (America/Lower_Princes,AST,-04:00:00,f)
 (America/Adak,HADT,-09:00:00,t)
 (America/Santiago,CLST,-03:00:00,t)
 (America/Catamarca,ART,-03:00:00,f)
 (America/Sao_Paulo,BRT,-03:00:00,f)
 (America/Swift_Current,CST,-06:00:00,f)
 (America/Shiprock,MDT,-06:00:00,t)
 (America/Nassau,EDT,-04:00:00,t)
 (America/Anchorage,AKDT,-08:00:00,t)
 (America/Porto_Acre,AMT,-04:00:00,f)
 (America/Halifax,ADT,-03:00:00,t)
 (America/Dominica,AST,-04:00:00,f)
 (America/St_Vincent,AST,-04:00:00,f)
 (America/Dawson,PDT,-07:00:00,t)
 (America/Montserrat,AST,-04:00:00,f)
 (America/Noronha,FNT,-02:00:00,f)
 (America/Mexico_City,CDT,-05:00:00,t)
 (America/Indiana/Petersburg,EDT,-04:00:00,t)
 (America/Indiana/Winamac,EDT,-04:00:00,t)
 (America/Indiana/Vincennes,EDT,-04:00:00,t)
 (America/Indiana/Vevay,EDT,-04:00:00,t)
 (America/Indiana/Indianapolis,EDT,-04:00:00,t)
 (America/Indiana/Tell_City,CDT,-05:00:00,t)
 (America/Indiana/Marengo,EDT,-04:00:00,t)
 (America/Indiana/Knox,CDT,-05:00:00,t)
 (America/Glace_Bay,ADT,-03:00:00,t)
 (America/Vancouver,PDT,-07:00:00,t)
 (America/Guayaquil,ECT,-05:00:00,f)
 (America/Virgin,AST,-04:00:00,f)
 (America/Iqaluit,EDT,-04:00:00,t)
 (America/Asuncion,PYT,-04:00:00,f)
 (America/Danmarkshavn,GMT,00:00:00,f)
 (America/Metlakatla,MeST,-08:00:00,f)
 (America/Kralendijk,AST,-04:00:00,f)
 (America/Guatemala,CST,-06:00:00,f)
 (America/Thunder_Bay,EDT,-04:00:00,t)
 (America/Fortaleza,BRT,-03:00:00,f)
 (America/Port-au-Prince,EDT,-04:00:00,t)
 (America/Ensenada,PDT,-07:00:00,t)
 (America/Scoresbysund,EGST,00:00:00,t)
 (America/Merida,CDT,-05:00:00,t)
 (America/Nome,AKDT,-08:00:00,t)
 (America/Godthab,WGST,-02:00:00,t)
 (America/Yellowknife,MDT,-06:00:00,t)
 (America/Coral_Harbour,EST,-05:00:00,f)
 (America/Havana,CDT,-04:00:00,t)
 (America/Manaus,AMT,-04:00:00,f)
 (America/Denver,MDT,-06:00:00,t)
 (America/Marigot,AST,-04:00:00,f)
 (America/Yakutat,AKDT,-08:00:00,t)
 (America/New_York,EDT,-04:00:00,t)
 (America/Miquelon,PMDT,-02:00:00,t)
 (America/Monterrey,CDT,-05:00:00,t)
 (America/Jujuy,ART,-03:00:00,f)
 (America/Belem,BRT,-03:00:00,f)
 (America/Antigua,AST,-04:00:00,f)
 (America/Ojinaga,MDT,-06:00:00,t)
 (America/Cancun,CDT,-05:00:00,t)
 (America/North_Dakota/Beulah,CDT,-05:00:00,t)
 (America/North_Dakota/Center,CDT,-05:00:00,t)
 (America/North_Dakota/New_Salem,CDT,-05:00:00,t)
 (America/Grenada,AST,-04:00:00,f)
 (America/Rainy_River,CDT,-05:00:00,t)
 (America/Indianapolis,EDT,-04:00:00,t)
 (America/Cambridge_Bay,MDT,-06:00:00,t)
 (America/Santa_Isabel,PDT,-07:00:00,t)
 (America/Toronto,EDT,-04:00:00,t)
 (America/Paramaribo,SRT,-03:00:00,f)
 (America/Montreal,EDT,-04:00:00,t)
 (America/Buenos_Aires,ART,-03:00:00,f)
 (America/Regina,CST,-06:00:00,f)
 (America/Porto_Velho,AMT,-04:00:00,f)
 (America/Atka,HADT,-09:00:00,t)
 (America/Port_of_Spain,AST,-04:00:00,f)
 (America/Juneau,AKDT,-08:00:00,t)
 (America/St_Johns,NDT,-02:30:00,t)
 (America/Inuvik,MDT,-06:00:00,t)
 (America/Grand_Turk,EDT,-04:00:00,t)
 (America/Goose_Bay,ADT,-03:00:00,t)
 (America/Caracas,VET,-04:30:00,f)
 (America/Los_Angeles,PDT,-07:00:00,t)
 (America/Campo_Grande,AMT,-04:00:00,f)
 (America/Dawson_Creek,MST,-07:00:00,f)
 (America/Recife,BRT,-03:00:00,f)
 (America/Santo_Domingo,AST,-04:00:00,f)
 (America/Cayman,EST,-05:00:00,f)
 (America/Winnipeg,CDT,-05:00:00,t)
 (America/St_Lucia,AST,-04:00:00,f)
 (America/Guadeloupe,AST,-04:00:00,f)
 (America/La_Paz,BOT,-04:00:00,f)
 (America/Rosario,ART,-03:00:00,f)
 (America/Aruba,AST,-04:00:00,f)
 (America/Matamoros,CDT,-05:00:00,t)
 (America/Tegucigalpa,CST,-06:00:00,f)
 (America/Whitehorse,PDT,-07:00:00,t)
 (America/Cayenne,GFT,-03:00:00,f)
 (America/Sitka,AKDT,-08:00:00,t)
 (America/Atikokan,EST,-05:00:00,f)
 (America/Anguilla,AST,-04:00:00,f)
 (America/Bogota,COT,-05:00:00,f)
 (America/Curacao,AST,-04:00:00,f)
 (America/Chicago,CDT,-05:00:00,t)
 (America/Puerto_Rico,AST,-04:00:00,f)
 (America/Araguaina,BRT,-03:00:00,f)
 (America/St_Thomas,AST,-04:00:00,f)
 (America/Phoenix,MST,-07:00:00,f)
 (America/Nipigon,EDT,-04:00:00,t)
 (America/Chihuahua,MDT,-06:00:00,t)
 (America/Detroit,EDT,-04:00:00,t)
 (America/Fort_Wayne,EDT,-04:00:00,t)
 (America/Maceio,BRT,-03:00:00,f)
 (America/Tijuana,PDT,-07:00:00,t)
 (America/El_Salvador,CST,-06:00:00,f)
 (America/Blanc-Sablon,AST,-04:00:00,f)
 (America/St_Barthelemy,AST,-04:00:00,f)
 (America/Knox_IN,CDT,-05:00:00,t)
 (America/Creston,MST,-07:00:00,f)
 (America/Bahia_Banderas,CDT,-05:00:00,t)
 (America/Boise,MDT,-06:00:00,t)
 (America/Tortola,AST,-04:00:00,f)
 (America/Pangnirtung,EDT,-04:00:00,t)
 (America/Rankin_Inlet,CDT,-05:00:00,t)
 (America/Jamaica,EST,-05:00:00,f)
 (America/St_Kitts,AST,-04:00:00,f)
 (America/Guyana,GYT,-04:00:00,f)
 (America/Lima,PET,-05:00:00,f)
 (America/Argentina/Mendoza,ART,-03:00:00,f)
 (America/Argentina/Cordoba,ART,-03:00:00,f)
 (America/Argentina/La_Rioja,ART,-03:00:00,f)
 (America/Argentina/Salta,ART,-03:00:00,f)
 (America/Argentina/San_Luis,WARST,-03:00:00,t)
 (America/Argentina/Catamarca,ART,-03:00:00,f)
 (America/Argentina/San_Juan,ART,-03:00:00,f)
 (America/Argentina/ComodRivadavia,ART,-03:00:00,f)
 (America/Argentina/Jujuy,ART,-03:00:00,f)
 (America/Argentina/Buenos_Aires,ART,-03:00:00,f)
 (America/Argentina/Tucuman,ART,-03:00:00,f)
 (America/Argentina/Rio_Gallegos,ART,-03:00:00,f)
 (America/Argentina/Ushuaia,ART,-03:00:00,f)
 (America/Mazatlan,MDT,-06:00:00,t)
 (America/Montevideo,UYT,-03:00:00,f)
 (America/Hermosillo,MST,-07:00:00,f)
 (America/Cuiaba,AMT,-04:00:00,f)
 (America/Bahia,BRT,-03:00:00,f)
 (America/Panama,EST,-05:00:00,f)
 (GB-Eire,BST,01:00:00,t)
 (Antarctica/Mawson,MAWT,05:00:00,f)
 (Antarctica/Rothera,ROTT,-03:00:00,f)
 (Antarctica/Davis,DAVT,07:00:00,f)
 (Antarctica/McMurdo,NZDT,13:00:00,t)
 (Antarctica/South_Pole,NZDT,13:00:00,t)
 (Antarctica/Syowa,SYOT,03:00:00,f)
 (Antarctica/Palmer,CLST,-03:00:00,t)
 (Antarctica/Casey,WST,08:00:00,f)
 (Antarctica/Macquarie,MIST,11:00:00,f)
 (Antarctica/Vostok,VOST,06:00:00,f)
 (Antarctica/DumontDUrville,DDUT,10:00:00,f)
 (Portugal,WEST,01:00:00,t)
 (Pacific/Fakaofo,TKT,13:00:00,f)
 (Pacific/Chatham,CHADT,13:45:00,t)
 (Pacific/Funafuti,TVT,12:00:00,f)
 (Pacific/Johnston,HST,-10:00:00,f)
 (Pacific/Samoa,SST,-11:00:00,f)
 (Pacific/Galapagos,GALT,-06:00:00,f)
 (Pacific/Noumea,NCT,11:00:00,f)
 (Pacific/Palau,PWT,09:00:00,f)
 (Pacific/Honolulu,HST,-10:00:00,f)
 (Pacific/Wallis,WFT,12:00:00,f)
 (Pacific/Port_Moresby,PGT,10:00:00,f)
 (Pacific/Chuuk,CHUT,10:00:00,f)
 (Pacific/Nauru,NRT,12:00:00,f)
 (Pacific/Easter,EASST,-05:00:00,t)
 (Pacific/Majuro,MHT,12:00:00,f)
 (Pacific/Guam,ChST,10:00:00,f)
 (Pacific/Pitcairn,PST,-08:00:00,f)
 (Pacific/Tahiti,TAHT,-10:00:00,f)
 (Pacific/Kiritimati,LINT,14:00:00,f)
 (Pacific/Wake,WAKT,12:00:00,f)
 (Pacific/Rarotonga,CKT,-10:00:00,f)
 (Pacific/Truk,CHUT,10:00:00,f)
 (Pacific/Enderbury,PHOT,13:00:00,f)
 (Pacific/Guadalcanal,SBT,11:00:00,f)
 (Pacific/Saipan,ChST,10:00:00,f)
 (Pacific/Fiji,FJT,12:00:00,f)
 (Pacific/Efate,VUT,11:00:00,f)
 (Pacific/Marquesas,MART,-09:30:00,f)
 (Pacific/Tarawa,GILT,12:00:00,f)
 (Pacific/Pago_Pago,SST,-11:00:00,f)
 (Pacific/Tongatapu,TOT,13:00:00,f)
 (Pacific/Niue,NUT,-11:00:00,f)
 (Pacific/Yap,CHUT,10:00:00,f)
 (Pacific/Kwajalein,MHT,12:00:00,f)
 (Pacific/Ponape,PONT,11:00:00,f)
 (Pacific/Apia,WSDT,14:00:00,t)
 (Pacific/Norfolk,NFT,11:30:00,f)
 (Pacific/Auckland,NZDT,13:00:00,t)
 (Pacific/Pohnpei,PONT,11:00:00,f)
 (Pacific/Gambier,GAMT,-09:00:00,f)
 (Pacific/Midway,SST,-11:00:00,f)
 (Pacific/Kosrae,KOST,11:00:00,f)
 (Chile/EasterIsland,EASST,-05:00:00,t)
 (Chile/Continental,CLST,-03:00:00,t)
 (GMT-0,GMT,00:00:00,f)
 (Libya,CEST,02:00:00,t)
 (MST,MST,-07:00:00,f)
 (UCT,UCT,00:00:00,f)
 (MET,MEST,02:00:00,t)
 (Etc/GMT-9,GMT-9,09:00:00,f)
 (Etc/GMT-3,GMT-3,03:00:00,f)
 (Etc/GMT-8,GMT-8,08:00:00,f)
 (Etc/GMT-0,GMT,00:00:00,f)
 (Etc/GMT+11,GMT+11,-11:00:00,f)
 (Etc/UCT,UCT,00:00:00,f)
 (Etc/Greenwich,GMT,00:00:00,f)
 (Etc/GMT-5,GMT-5,05:00:00,f)
 (Etc/GMT-1,GMT-1,01:00:00,f)
 (Etc/Universal,UTC,00:00:00,f)
 (Etc/GMT+8,GMT+8,-08:00:00,f)
 (Etc/GMT-12,GMT-12,12:00:00,f)
 (Etc/Zulu,UTC,00:00:00,f)
 (Etc/GMT+9,GMT+9,-09:00:00,f)
 (Etc/UTC,UTC,00:00:00,f)
 (Etc/GMT+4,GMT+4,-04:00:00,f)
 (Etc/GMT-10,GMT-10,10:00:00,f)
 (Etc/GMT-4,GMT-4,04:00:00,f)
 (Etc/GMT-11,GMT-11,11:00:00,f)
 (Etc/GMT,GMT,00:00:00,f)
 (Etc/GMT+3,GMT+3,-03:00:00,f)
 (Etc/GMT+0,GMT,00:00:00,f)
 (Etc/GMT+10,GMT+10,-10:00:00,f)
 (Etc/GMT-6,GMT-6,06:00:00,f)
 (Etc/GMT+7,GMT+7,-07:00:00,f)
 (Etc/GMT+6,GMT+6,-06:00:00,f)
 (Etc/GMT-13,GMT-13,13:00:00,f)
 (Etc/GMT0,GMT,00:00:00,f)
 (Etc/GMT+12,GMT+12,-12:00:00,f)
 (Etc/GMT-7,GMT-7,07:00:00,f)
 (Etc/GMT+2,GMT+2,-02:00:00,f)
 (Etc/GMT-14,GMT-14,14:00:00,f)
 (Etc/GMT+5,GMT+5,-05:00:00,f)
 (Etc/GMT-2,GMT-2,02:00:00,f)
 (Etc/GMT+1,GMT+1,-01:00:00,f)
 (Indian/Mahe,SCT,04:00:00,f)
 (Indian/Maldives,MVT,05:00:00,f)
 (Indian/Comoro,EAT,03:00:00,f)
 (Indian/Chagos,IOT,06:00:00,f)
 (Indian/Antananarivo,EAT,03:00:00,f)
 (Indian/Mayotte,EAT,03:00:00,f)
 (Indian/Cocos,CCT,06:30:00,f)
 (Indian/Mauritius,MUT,04:00:00,f)
 (Indian/Kerguelen,TFT,05:00:00,f)
 (Indian/Reunion,RET,04:00:00,f)
 (Indian/Christmas,CXT,07:00:00,f)
 (MST7MDT,MDT,-06:00:00,t)
 (W-SU,MSK,04:00:00,f)
 (EET,EEST,03:00:00,t)
 (Greenwich,GMT,00:00:00,f)
 (Canada/Pacific,PDT,-07:00:00,t)
 (Canada/Central,CDT,-05:00:00,t)
 (Canada/Mountain,MDT,-06:00:00,t)
 (Canada/Saskatchewan,CST,-06:00:00,f)
 (Canada/Eastern,EDT,-04:00:00,t)
 (Canada/Yukon,PDT,-07:00:00,t)
 (Canada/Atlantic,ADT,-03:00:00,t)
 (Canada/Newfoundland,NDT,-02:30:00,t)
 (Canada/East-Saskatchewan,CST,-06:00:00,f)
 (Iran,IRST,03:30:00,f)
 (Arctic/Longyearbyen,CEST,02:00:00,t)
 (Eire,IST,01:00:00,t)
 (EST,EST,-05:00:00,f)
 (GB,BST,01:00:00,t)
 (PRC,CST,08:00:00,f)
 (Universal,UTC,00:00:00,f)
 (Mexico/BajaNorte,PDT,-07:00:00,t)
 (Mexico/BajaSur,MDT,-06:00:00,t)
 (Mexico/General,CDT,-05:00:00,t)
 (Navajo,MDT,-06:00:00,t)
 (Japan,JST,09:00:00,f)
 (Iceland,GMT,00:00:00,f)
 (Zulu,UTC,00:00:00,f)
 (Israel,IDT,03:00:00,t)
 (Singapore,SGT,08:00:00,f)
 (Atlantic/Azores,AZOST,00:00:00,t)
 (Atlantic/Jan_Mayen,CEST,02:00:00,t)
 (Atlantic/Reykjavik,GMT,00:00:00,f)
 (Atlantic/Madeira,WEST,01:00:00,t)
 (Atlantic/South_Georgia,GST,-02:00:00,f)
 (Atlantic/St_Helena,GMT,00:00:00,f)
 (Atlantic/Cape_Verde,CVT,-01:00:00,f)
 (Atlantic/Faroe,WEST,01:00:00,t)
 (Atlantic/Bermuda,ADT,-03:00:00,t)
 (Atlantic/Stanley,FKST,-03:00:00,f)
 (Atlantic/Faeroe,WEST,01:00:00,t)
 (Atlantic/Canary,WEST,01:00:00,t)
 (UTC,UTC,00:00:00,f)
 (NZ-CHAT,CHADT,13:45:00,t)
 (ROC,CST,08:00:00,f)
 (HST,HST,-10:00:00,f)
 (Egypt,EET,02:00:00,f)
 (GMT,GMT,00:00:00,f)
 (Poland,CEST,02:00:00,t)
 (NZ,NZDT,13:00:00,t)
 (Hongkong,HKT,08:00:00,f)
 (WET,WEST,01:00:00,t)
 (GMT+0,GMT,00:00:00,f)
 (Turkey,EEST,03:00:00,t)
 (Kwajalein,MHT,12:00:00,f)
 (CET,CEST,02:00:00,t)
 (US/Samoa,SST,-11:00:00,f)
 (US/Pacific,PDT,-07:00:00,t)
 (US/East-Indiana,EDT,-04:00:00,t)
 (US/Michigan,EDT,-04:00:00,t)
 (US/Arizona,MST,-07:00:00,f)
 (US/Hawaii,HST,-10:00:00,f)
 (US/Central,CDT,-05:00:00,t)
 (US/Mountain,MDT,-06:00:00,t)
 (US/Eastern,EDT,-04:00:00,t)
 (US/Indiana-Starke,CDT,-05:00:00,t)
 (US/Pacific-New,PDT,-07:00:00,t)
 (US/Aleutian,HADT,-09:00:00,t)
 (US/Alaska,AKDT,-08:00:00,t)
 (Brazil/DeNoronha,FNT,-02:00:00,f)
 (Brazil/Acre,AMT,-04:00:00,f)
 (Brazil/East,BRT,-03:00:00,f)
 (Brazil/West,AMT,-04:00:00,f)
 (Asia/Dubai,GST,04:00:00,f)
 (Asia/Beirut,EEST,03:00:00,t)
 (Asia/Macao,CST,08:00:00,f)
 (Asia/Jayapura,EIT,09:00:00,f)
 (Asia/Colombo,IST,05:30:00,f)
 (Asia/Vladivostok,VLAT,11:00:00,f)
 (Asia/Istanbul,EEST,03:00:00,t)
 (Asia/Riyadh88,zzz,03:07:04,f)
 (Asia/Novosibirsk,NOVT,07:00:00,f)
 (Asia/Aqtau,AQTT,05:00:00,f)
 (Asia/Tokyo,JST,09:00:00,f)
 (Asia/Dacca,BDT,06:00:00,f)
 (Asia/Ulaanbaatar,ULAT,08:00:00,f)
 (Asia/Tbilisi,GET,04:00:00,f)
 (Asia/Tehran,IRST,03:30:00,f)
 (Asia/Ujung_Pandang,CIT,08:00:00,f)
 (Asia/Pyongyang,KST,09:00:00,f)
 (Asia/Karachi,PKT,05:00:00,f)
 (Asia/Ashgabat,TMT,05:00:00,f)
 (Asia/Bishkek,KGT,06:00:00,f)
 (Asia/Bahrain,AST,03:00:00,f)
 (Asia/Kuching,MYT,08:00:00,f)
 (Asia/Seoul,KST,09:00:00,f)
 (Asia/Sakhalin,SAKT,11:00:00,f)
 (Asia/Hovd,HOVT,07:00:00,f)
 (Asia/Riyadh,AST,03:00:00,f)
 (Asia/Baghdad,AST,03:00:00,f)
 (Asia/Manila,PHT,08:00:00,f)
 (Asia/Calcutta,IST,05:30:00,f)
 (Asia/Choibalsan,CHOT,08:00:00,f)
 (Asia/Shanghai,CST,08:00:00,f)
 (Asia/Dhaka,BDT,06:00:00,f)
 (Asia/Khandyga,YAKT,10:00:00,f)
 (Asia/Nicosia,EEST,03:00:00,t)
 (Asia/Yerevan,AMT,04:00:00,f)
 (Asia/Vientiane,ICT,07:00:00,f)
 (Asia/Yekaterinburg,YEKT,06:00:00,f)
 (Asia/Katmandu,NPT,05:45:00,f)
 (Asia/Chongqing,CST,08:00:00,f)
 (Asia/Riyadh89,zzz,03:07:04,f)
 (Asia/Ust-Nera,VLAT,11:00:00,f)
 (Asia/Aqtobe,AQTT,05:00:00,f)
 (Asia/Almaty,ALMT,06:00:00,f)
 (Asia/Dushanbe,TJT,05:00:00,f)
 (Asia/Kashgar,CST,08:00:00,f)
 (Asia/Singapore,SGT,08:00:00,f)
 (Asia/Tel_Aviv,IDT,03:00:00,t)
 (Asia/Irkutsk,IRKT,09:00:00,f)
 (Asia/Phnom_Penh,ICT,07:00:00,f)
 (Asia/Novokuznetsk,NOVT,07:00:00,f)
 (Asia/Dili,TLT,09:00:00,f)
 (Asia/Ulan_Bator,ULAT,08:00:00,f)
 (Asia/Harbin,CST,08:00:00,f)
 (Asia/Damascus,EEST,03:00:00,t)
 (Asia/Yakutsk,YAKT,10:00:00,f)
 (Asia/Jerusalem,IDT,03:00:00,t)
 (Asia/Kolkata,IST,05:30:00,f)
 (Asia/Anadyr,ANAT,12:00:00,f)
 (Asia/Samarkand,UZT,05:00:00,f)
 (Asia/Qatar,AST,03:00:00,f)
 (Asia/Ho_Chi_Minh,ICT,07:00:00,f)
 (Asia/Thimphu,BTT,06:00:00,f)
 (Asia/Chungking,CST,08:00:00,f)
 (Asia/Gaza,EET,02:00:00,f)
 (Asia/Kathmandu,NPT,05:45:00,f)
 (Asia/Ashkhabad,TMT,05:00:00,f)
 (Asia/Qyzylorda,QYZT,06:00:00,f)
 (Asia/Aden,AST,03:00:00,f)
 (Asia/Baku,AZST,05:00:00,t)
 (Asia/Magadan,MAGT,12:00:00,f)
 (Asia/Pontianak,WIT,07:00:00,f)
 (Asia/Macau,CST,08:00:00,f)
 (Asia/Hong_Kong,HKT,08:00:00,f)
 (Asia/Kuwait,AST,03:00:00,f)
 (Asia/Brunei,BNT,08:00:00,f)
 (Asia/Amman,EEST,03:00:00,t)
 (Asia/Makassar,CIT,08:00:00,f)
 (Asia/Urumqi,CST,08:00:00,f)
 (Asia/Oral,ORAT,05:00:00,f)
 (Asia/Kuala_Lumpur,MYT,08:00:00,f)
 (Asia/Jakarta,WIT,07:00:00,f)
 (Asia/Taipei,CST,08:00:00,f)
 (Asia/Bangkok,ICT,07:00:00,f)
 (Asia/Omsk,OMST,07:00:00,f)
 (Asia/Saigon,ICT,07:00:00,f)
 (Asia/Muscat,GST,04:00:00,f)
 (Asia/Krasnoyarsk,KRAT,08:00:00,f)
 (Asia/Tashkent,UZT,05:00:00,f)
 (Asia/Riyadh87,zzz,03:07:04,f)
 (Asia/Hebron,EET,02:00:00,f)
 (Asia/Rangoon,MMT,06:30:00,f)
 (Asia/Thimbu,BTT,06:00:00,f)
 (Asia/Kabul,AFT,04:30:00,f)
 (Asia/Kamchatka,PETT,12:00:00,f)
 (GMT0,GMT,00:00:00,f)
 (Mideast/Riyadh88,zzz,03:07:04,f)
 (Mideast/Riyadh89,zzz,03:07:04,f)
 (Mideast/Riyadh87,zzz,03:07:04,f)
 (Australia/Yancowinna,CST,09:30:00,f)
 (Australia/Brisbane,EST,10:00:00,f)
 (Australia/South,CST,09:30:00,f)
 (Australia/Eucla,CWST,08:45:00,f)
 (Australia/Queensland,EST,10:00:00,f)
 (Australia/Adelaide,CST,09:30:00,f)
 (Australia/Darwin,CST,09:30:00,f)
 (Australia/ACT,EST,10:00:00,f)
 (Australia/Tasmania,EST,10:00:00,f)
 (Australia/Currie,EST,10:00:00,f)
 (Australia/Lindeman,EST,10:00:00,f)
 (Australia/LHI,LHST,10:30:00,f)
 (Australia/Canberra,EST,10:00:00,f)
 (Australia/Perth,WST,08:00:00,f)
 (Australia/Lord_Howe,LHST,10:30:00,f)
 (Australia/West,WST,08:00:00,f)
 (Australia/Melbourne,EST,10:00:00,f)
 (Australia/NSW,EST,10:00:00,f)
 (Australia/Victoria,EST,10:00:00,f)
 (Australia/North,CST,09:30:00,f)
 (Australia/Hobart,EST,10:00:00,f)
 (Australia/Sydney,EST,10:00:00,f)
 (Australia/Broken_Hill,CST,09:30:00,f)
 (ROK,KST,09:00:00,f)
 (Jamaica,EST,-05:00:00,f)
 (Europe/Samara,SAMT,04:00:00,f)
 (Europe/Oslo,CEST,02:00:00,t)
 (Europe/Istanbul,EEST,03:00:00,t)
 (Europe/Malta,CEST,02:00:00,t)
 (Europe/Amsterdam,CEST,02:00:00,t)
 (Europe/Simferopol,EEST,03:00:00,t)
 (Europe/Brussels,CEST,02:00:00,t)
 (Europe/Bratislava,CEST,02:00:00,t)
 (Europe/Uzhgorod,EEST,03:00:00,t)
 (Europe/Busingen,CEST,02:00:00,t)
 (Europe/Mariehamn,EEST,03:00:00,t)
 (Europe/Rome,CEST,02:00:00,t)
 (Europe/Monaco,CEST,02:00:00,t)
 (Europe/Warsaw,CEST,02:00:00,t)
 (Europe/Stockholm,CEST,02:00:00,t)
 (Europe/Copenhagen,CEST,02:00:00,t)
 (Europe/Vaduz,CEST,02:00:00,t)
 (Europe/Podgorica,CEST,02:00:00,t)
 (Europe/Moscow,MSK,04:00:00,f)
 (Europe/Madrid,CEST,02:00:00,t)
 (Europe/Berlin,CEST,02:00:00,t)
 (Europe/Tiraspol,EEST,03:00:00,t)
 (Europe/Belgrade,CEST,02:00:00,t)
 (Europe/Zaporozhye,EEST,03:00:00,t)
 (Europe/Isle_of_Man,BST,01:00:00,t)
 (Europe/Nicosia,EEST,03:00:00,t)
 (Europe/Vienna,CEST,02:00:00,t)
 (Europe/Zurich,CEST,02:00:00,t)
 (Europe/Tallinn,EEST,03:00:00,t)
 (Europe/Gibraltar,CEST,02:00:00,t)
 (Europe/Skopje,CEST,02:00:00,t)
 (Europe/Andorra,CEST,02:00:00,t)
 (Europe/Sofia,EEST,03:00:00,t)
 (Europe/Ljubljana,CEST,02:00:00,t)
 (Europe/Chisinau,EEST,03:00:00,t)
 (Europe/Tirane,CEST,02:00:00,t)
 (Europe/London,BST,01:00:00,t)
 (Europe/Prague,CEST,02:00:00,t)
 (Europe/Dublin,IST,01:00:00,t)
 (Europe/Lisbon,WEST,01:00:00,t)
 (Europe/Guernsey,BST,01:00:00,t)
 (Europe/Vilnius,EEST,03:00:00,t)
 (Europe/Volgograd,VOLT,04:00:00,f)
 (Europe/Luxembourg,CEST,02:00:00,t)
 (Europe/Belfast,BST,01:00:00,t)
 (Europe/Kiev,EEST,03:00:00,t)
 (Europe/Minsk,FET,03:00:00,f)
 (Europe/Vatican,CEST,02:00:00,t)
 (Europe/Kaliningrad,FET,03:00:00,f)
 (Europe/Sarajevo,CEST,02:00:00,t)
 (Europe/Zagreb,CEST,02:00:00,t)
 (Europe/Bucharest,EEST,03:00:00,t)
 (Europe/Paris,CEST,02:00:00,t)
 (Europe/Budapest,CEST,02:00:00,t)
 (Europe/Jersey,BST,01:00:00,t)
 (Europe/Athens,EEST,03:00:00,t)
 (Europe/San_Marino,CEST,02:00:00,t)
 (Europe/Helsinki,EEST,03:00:00,t)
 (Europe/Riga,EEST,03:00:00,t)
 (Cuba,CDT,-04:00:00,t)
(584 rows)

また、サポートされているタイムゾーンの省略形の一覧を表示するにはpg_timezone_abbrevs()を使います。

# SELECT pg_timezone_abbrevs();
 pg_timezone_abbrevs 
---------------------
 (ACSST,10:30:00,t)
 (ACST,-04:00:00,t)
 (ACT,-05:00:00,f)
 (ADT,-03:00:00,t)
 (AESST,11:00:00,t)
 (AEST,10:00:00,f)
 (AFT,04:30:00,f)
 (AKDT,-08:00:00,t)
 (AKST,-09:00:00,f)
 (ALMST,07:00:00,t)
 (ALMT,06:00:00,f)
 (AMST,05:00:00,t)
 (AMT,04:00:00,f)
 (ANAST,13:00:00,t)
 (ANAT,12:00:00,f)
 (ARST,-02:00:00,t)
 (ART,-03:00:00,f)
 (AST,-04:00:00,f)
 (AWSST,09:00:00,t)
 (AWST,08:00:00,f)
 (AZOST,00:00:00,t)
 (AZOT,-01:00:00,f)
 (AZST,05:00:00,t)
 (AZT,04:00:00,f)
 (BDST,02:00:00,t)
 (BDT,06:00:00,f)
 (BNT,08:00:00,f)
 (BORT,08:00:00,f)
 (BOT,-04:00:00,f)
 (BRA,-03:00:00,f)
 (BRST,-02:00:00,t)
 (BRT,-03:00:00,f)
 (BST,01:00:00,t)
 (BTT,06:00:00,f)
 (CADT,10:30:00,t)
 (CAST,09:30:00,f)
 (CCT,08:00:00,f)
 (CDT,-05:00:00,t)
 (CEST,02:00:00,t)
 (CET,01:00:00,f)
 (CETDST,02:00:00,t)
 (CHADT,13:45:00,t)
 (CHAST,12:45:00,f)
 (CHUT,10:00:00,f)
 (CKT,12:00:00,f)
 (CLST,-03:00:00,t)
 (CLT,-04:00:00,f)
 (COT,-05:00:00,f)
 (CST,-06:00:00,f)
 (CXT,07:00:00,f)
 (DAVT,07:00:00,f)
 (DDUT,10:00:00,f)
 (EASST,-05:00:00,t)
 (EAST,-06:00:00,f)
 (EAT,03:00:00,f)
 (EDT,-04:00:00,t)
 (EEST,03:00:00,t)
 (EET,02:00:00,f)
 (EETDST,03:00:00,t)
 (EGST,00:00:00,t)
 (EGT,-01:00:00,f)
 (EST,-05:00:00,f)
 (FET,03:00:00,f)
 (FJST,-13:00:00,t)
 (FJT,-12:00:00,f)
 (FKST,-03:00:00,t)
 (FKT,-04:00:00,f)
 (FNST,-01:00:00,t)
 (FNT,-02:00:00,f)
 (GALT,-06:00:00,f)
 (GAMT,-09:00:00,f)
 (GEST,04:00:00,t)
 (GET,04:00:00,f)
 (GFT,-03:00:00,f)
 (GILT,12:00:00,f)
 (GMT,00:00:00,f)
 (GYT,-04:00:00,f)
 (HKT,08:00:00,f)
 (HST,-10:00:00,f)
 (ICT,07:00:00,f)
 (IDT,03:00:00,t)
 (IOT,06:00:00,f)
 (IRKST,09:00:00,t)
 (IRKT,09:00:00,f)
 (IRT,03:30:00,f)
 (IST,02:00:00,f)
 (JAYT,09:00:00,f)
 (JST,09:00:00,f)
 (KDT,10:00:00,t)
 (KGST,06:00:00,t)
 (KGT,06:00:00,f)
 (KOST,11:00:00,f)
 (KRAST,08:00:00,t)
 (KRAT,08:00:00,f)
 (KST,09:00:00,f)
 (LHDT,11:00:00,t)
 (LHST,10:30:00,f)
 (LIGT,10:00:00,f)
 (LINT,14:00:00,f)
 (LKT,06:00:00,f)
 (MAGST,12:00:00,t)
 (MAGT,12:00:00,f)
 (MART,-09:30:00,f)
 (MAWT,05:00:00,f)
 (MDT,-06:00:00,t)
 (MEST,02:00:00,t)
 (MET,01:00:00,f)
 (METDST,02:00:00,t)
 (MEZ,01:00:00,f)
 (MHT,12:00:00,f)
 (MMT,06:30:00,f)
 (MPT,10:00:00,f)
 (MSD,04:00:00,t)
 (MSK,04:00:00,f)
 (MST,-07:00:00,f)
 (MUST,05:00:00,t)
 (MUT,04:00:00,f)
 (MVT,05:00:00,f)
 (MYT,08:00:00,f)
 (NDT,-02:30:00,t)
 (NFT,-03:30:00,f)
 (NOVST,07:00:00,t)
 (NOVT,07:00:00,f)
 (NPT,05:45:00,f)
 (NST,-03:30:00,f)
 (NUT,-11:00:00,f)
 (NZDT,13:00:00,t)
 (NZST,12:00:00,f)
 (NZT,12:00:00,f)
 (OMSST,07:00:00,t)
 (OMST,07:00:00,f)
 (PDT,-07:00:00,t)
 (PET,-05:00:00,f)
 (PETST,13:00:00,t)
 (PETT,12:00:00,f)
 (PGT,10:00:00,f)
 (PHOT,13:00:00,f)
 (PHT,08:00:00,f)
 (PKST,06:00:00,t)
 (PKT,05:00:00,f)
 (PMDT,-02:00:00,t)
 (PMST,-03:00:00,f)
 (PONT,11:00:00,f)
 (PST,-08:00:00,f)
 (PWT,09:00:00,f)
 (PYST,-03:00:00,t)
 (PYT,-04:00:00,f)
 (RET,04:00:00,f)
 (SADT,10:30:00,t)
 (SAST,09:30:00,f)
 (SCT,04:00:00,f)
 (SGT,08:00:00,f)
 (TAHT,-10:00:00,f)
 (TFT,05:00:00,f)
 (TJT,05:00:00,f)
 (TKT,13:00:00,f)
 (TMT,05:00:00,f)
 (TOT,13:00:00,f)
 (TRUT,10:00:00,f)
 (TVT,12:00:00,f)
 (UCT,00:00:00,f)
 (ULAST,09:00:00,t)
 (ULAT,08:00:00,f)
 (UT,00:00:00,f)
 (UTC,00:00:00,f)
 (UYST,-02:00:00,t)
 (UYT,-03:00:00,f)
 (UZST,06:00:00,t)
 (UZT,05:00:00,f)
 (VET,-04:30:00,f)
 (VLAST,11:00:00,t)
 (VLAT,11:00:00,f)
 (VOLT,04:00:00,f)
 (VUT,11:00:00,f)
 (WADT,08:00:00,t)
 (WAKT,12:00:00,f)
 (WAST,07:00:00,f)
 (WAT,01:00:00,f)
 (WDT,09:00:00,t)
 (WET,00:00:00,f)
 (WETDST,01:00:00,t)
 (WFT,12:00:00,f)
 (WGST,-02:00:00,t)
 (WGT,-03:00:00,f)
 (YAKST,10:00:00,t)
 (YAKT,10:00:00,f)
 (YAPT,10:00:00,f)
 (YEKST,06:00:00,t)
 (YEKT,06:00:00,f)
 (Z,00:00:00,f)
 (ZULU,00:00:00,f)
(191 rows)

IANAにはタイムゾーンに関する情報も公開されていますので必要に応じてこちらの内容もご確認ください。

• IANA — Time Zone Database

タイムゾーンのフォーマットとしては以下のような形がサポートされているようです。

データ型: TIMESTAMPTZに関するその他の詳細については以下のような挙動、内容があります。

• 『秒』の部分の小数部は最大桁数6桁までを格納します。
• TIMESTAMPTZ値はテーブル内ではUTCとして格納されます。
• 日付型データや部分的なタイムスタンプ情報を持つ日付型データをTIMESTAMPTZ型の列に投入すると、値は不足部分を補う形でタイムスタンプの値に暗黙的に変換されます。(下記はTIMESTAMP型で試した形ですが、挙動としてはTIMESTAMPTZ型も同じ内容かと)

# CREATE TABLE public.tztest ( tz TIMESTAMP );
CREATE TABLE
# INSERT INTO public.tztest VALUES('2016/10/01');
INSERT
# INSERT INTO public.tztest VALUES('2016/10/01 12:00');
INSERT
# INSERT INTO public.tztest VALUES('2016/10/01 12:34');
INSERT
# INSERT INTO public.tztest VALUES('2016/10/01 12:34:56');
INSERT
# SELECT * FROM public.tztest ORDER BY tz;
         tz          
---------------------
 2016-10-01 00:00:00
 2016-10-01 12:00:00
 2016-10-01 12:34:00
 2016-10-01 12:34:56
(4 rows)

TIMESTAMPTZを元にした比較用の関数も新たに利用可能となるようです。(DATE_CMP_TIMESTAMPTZとTIMESTAMP_CMP_TIMESTAMPTZ)

• DATE_CMP_TIMESTAMPTZ Function - Amazon Redshift
• TIMESTAMP_CMP_TIMESTAMPTZ Function - Amazon Redshift

まとめ

新しいデータ型『TIMESTAMPTZ』が利用可能となる事で、異なるタイムゾーンのデータをより便利な形で一度に扱う事が可能となりました。複数のタイムゾーンに跨るようなデータ管理を行う場合、このデータ型を用いる事に拠って効果的・効率的な管理が出来そうですね。こちらからは以上です。